package com.iris.live.services.data.repositories.impl;

import com.iris.live.services.data.models.TFinanceCheckModel;
import com.iris.live.services.data.models.用户Model;
import com.iris.live.services.data.repositories.FinanceCheckRepositorySql;
import com.iris.live.services.models.PageData;
import com.iris.live.services.models.RequestBase;
import com.iris.live.services.services.DataCheckService;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: wangzhenyan
 * 2017/5/22 15:32
 */
@Repository
public class FinanceCheckRepositorySqlImpl extends BaseRepository implements FinanceCheckRepositorySql {

    @Override
    public List<TFinanceCheckModel> findFinanceCheckByImportBatchAndUser(String importBatch, 用户Model user) {
        String sql = "SELECT a.* FROM t_finance_check a \n" +
                "WHERE a.import_batch = :importBatch\n" +
                "AND a.permit = :permit" ;
        Map<String,Object> params = new HashMap<>();
        params.put("importBatch",importBatch);
        params.put("permit",user.get许可());
        return this.getList(sql,params);
    }

    @Override
    public PageData<Map<String, Object>> getContrastResultOfFinance(RequestBase req, String importBatch, String permit) {
        String sql = "SELECT\n" +
                "\tf.id,\n" +
                "\tf.vin,\n" +
                "CASE WHEN f.compare_result = '03' THEN NULL ELSE f.import_loan_cust_name END AS importLoanCustName,\n" +
                "CASE WHEN f.compare_result = '03' THEN NULL ELSE f.import_loan_approval_date END AS importLoanApprovalDate,\n" +
                "CASE WHEN f.compare_result = '03' THEN NULL ELSE f.import_finance_company END AS importFinanceCompany,\n" +
                "CASE WHEN f.compare_result = '03' THEN NULL ELSE f.import_deposit_payment END AS importDepositPayment,\n" +
                "CASE WHEN f.compare_result = '03' THEN NULL ELSE f.import_down_payment END AS importDownPayment,\n" +
                "CASE WHEN f.compare_result = '03' THEN NULL ELSE f.import_loan_payment END AS importLoanPayment,\n" +
                "CASE WHEN f.compare_result = '03' THEN NULL ELSE f.import_loan_term END AS importLoanTerm,\n" +
                "CASE WHEN f.compare_result = '03' THEN NULL ELSE f.import_yearly_rate END AS importYearlyRate,\n" +
                "CASE WHEN f.compare_result = '03' THEN NULL ELSE f.import_BMBS_discount END AS importBmbsDiscount,\n" +
                "CASE WHEN f.compare_result = '03' THEN NULL ELSE f.import_dealer_discount END AS importDealerDiscount,\n" +
                "CASE WHEN f.compare_result = '03' THEN NULL ELSE f.import_account_date END AS importAccountDate,\n" +
                "CASE WHEN f.compare_result = '03' THEN NULL ELSE f.import_finance_fee END AS importFinanceFee,\n" +
                "CASE WHEN f.compare_result = '03' THEN NULL ELSE f.import_finance_service_fee END AS importFinanceServiceFee,\n" +
                "CASE WHEN f.deduction_loan_cust_name = 0 THEN '1' ELSE '0' END AS deductionLoanCustName,\n" +
                "CASE WHEN f.deduction_loan_approval_date = 0 THEN '1' ELSE '0' END AS deductionLoanApprovalDate,\n" +
                "CASE WHEN f.deduction_finance_company = 0 THEN '1' ELSE '0' END AS deductionFinanceCompany,\n" +
                "CASE WHEN f.deduction_deposit_payment = 0 THEN '1' ELSE '0' END AS deductionDepositPayment,\n" +
                "CASE WHEN f.deduction_down_payment = 0 THEN '1' ELSE '0' END AS deductionDownPayment,\n" +
                "CASE WHEN f.deduction_loan_payment = 0 THEN '1' ELSE '0' END AS deductionLoanPayment,\n" +
                "CASE WHEN f.deduction_loan_term = 0 THEN '1' ELSE '0' END AS deductionLoanTerm,\n" +
                "CASE WHEN f.deduction_yearly_rate = 0 THEN '1' ELSE '0' END AS deductionYearlyRate,\n" +
                "CASE WHEN f.deduction_BMBS_discount = 0 THEN '1' ELSE '0' END AS deductionBmbsDiscount,\n" +
                "CASE WHEN f.deduction_dealer_discount = 0 THEN '1' ELSE '0' END AS deductionDealerDiscount,\n" +
                "CASE WHEN f.deduction_account_date = 0 THEN '1' ELSE '0' END AS deductionAccountDate,\n" +
                "CASE WHEN f.deduction_finance_fee = 0 THEN '1' ELSE '0' END AS deductionFinanceFee,\n" +
                "CASE WHEN f.deduction_finance_service_fee = 0 THEN '1' ELSE '0' END AS deductionFinanceServiceFee,\n" +
                "\tf.compare_result AS compareResult,\n" +
                "\tf.total_score AS totalScore\n" +
                "FROM t_finance_check f \n" +
                "LEFT JOIN 用户 b ON f.import_user = b.`用户名`\n" +
                "WHERE f.import_batch = :importBatch\n" +
                "AND f.permit = :permit\n" +
                "AND f.compare_result <> '04'" ;  //表格为空，系统为空（不显示）
//                "AND b.角色 like :角色";
        Map<String,Object> params = new HashMap<>();
        params.put("importBatch",importBatch);
        params.put("permit",permit);
//        params.put("角色","%金融%");    //金融顾问和金融保险经理
        return getPage(sql,params,req.getPageSize(),req.getPageIndex());
    }

    @Override
    public PageData getRankingOfFinance(DataCheckService.RankingRequest req) {
        String sql = "SELECT cast(@rowNum \\:= @rowNum + 1 AS DECIMAL(3,0))AS ranking, " +
                "  f.permit, " +
                "  f.`经销商名` AS distributor, " +
                "  f.`大区`, " +
                "  IFNULL(score,'0') AS score " +
                "FROM ( " +
                "  SELECT  " +
                "  @rowNum \\:= 0 AS ranking, " +
                "  a.permit, " +
                "  b.`经销商名`, " +
                "  b.`大区`, " +
                "  cast(avg(a.total_score) AS DECIMAL(5,2)) score , " +
                "  a.import_batch " +
                "  FROM  " +
                "    ( " +
                "    SELECT * FROM t_finance_check WHERE cast(import_batch AS date) >= cast(:startBatch AS date) AND cast(import_batch as date) <= cast(:endBatch AS date)  " +
                "    ) a " +
                "LEFT JOIN `lsh经销商表` b ON a.permit = b.`许可_BMBS` " +
                "GROUP BY a.permit " +
                "HAVING b.`大区` = ( " +
                "    SELECT c.大区 from `lsh经销商表` c WHERE c.`许可_BMBS` = :permit)     " +
                "ORDER BY AVG(a.total_score) DESC  " +
                ") f";

        Map<String,Object> params = new HashMap<>();
        params.put("permit",req.permit); //许可
        params.put("startBatch",req.startBatch); // 开始批次
        params.put("endBatch",req.endBatch); //结束批次
        return this.getPage(sql,params,req.getPageSize(),req.getPageIndex());
    }
}

